﻿/****** Object:  UserDefinedFunction [dbo].[fc_T_LayThongTinNhapXuat]    Script Date: 03/23/2015 16:22:13 ******/
CREATE function [dbo].[fc_T_LayThongTinNhapXuat]
(
	@tuNgay datetime,
	@denNgay datetime
)
returns table
as
	return(
		select				vt.VatTu,
							vt.MaKyHieu,
							dv.DVT,							                 
							t.Gia,
							ct.SLKH,
							ct.ThanhTien,
							n.KH_NguonVatTu,
							hd.SoHopDong,
							sp.KH_SanPham,
							t.SoTheKho + '-' + CAST(t.Code as varchar(5)) as SoTheKho_Code,
							t.TangKeKhoang as ViTri, 
							CASE p.NX
								WHEN 'N' THEN hd.SoHopDong
								ELSE t.NguonGoc
							 END as NguonGoc,
							CASE 
								WHEN MONTH (p.NgayPhieu)> 9 THEN CAST(p.SoPhieu as varchar(10)) + p.DuoiPhieu + '/' + CAST (MONTH(p.NgayPhieu) as varchar(2)) + '-' + RIGHT(CAST(YEAR(p.NgayPhieu) as varchar(4)), 2)
								ELSE CAST(p.SoPhieu as varchar(10)) + p.DuoiPhieu+ '/0' + CAST (MONTH(p.NgayPhieu) as varchar(2)) + '-' + RIGHT(CAST(YEAR(p.NgayPhieu) as varchar(4)), 2)
							END as SoPhieu,
							p.NgayPhieu,
							p.NX
	from tblDPNX p inner join tblSLNX ct on p.ID_DPNX = ct.ID_DPNX
	left join tblSanPham sp on p.ID_SanPham = sp.ID_SanPham
	inner join tblTon t on ct.ID_MatHang = t.ID_MatHang
	inner join tblVatTu vt on t.ID_VatTu = vt.ID_VatTu
	inner join tblDVT dv on vt.ID_DVT = dv.ID_DVT
	left join tblNguonVatTu n on t.ID_NguonVatTu = n.ID_NguonVatTu
	left join tblHopDong hd on hd.ID_HopDong = t.ID_HopDong	
	where p.NgayPhieu between @tuNgay and @denNgay
	)
GO

